Connect the HR API to Power BI: example
While we do not provide support for Power BI development, as it is not an IRIS product, we understand many businesses use it and want to integrate it with the IRIS HR API.
This topic provides an example of how to integrate the IRIS HR API into Power BI. However, it is not a complete solution, although, you can review the example and build on it.
This method uses Microsoft's Power Query (opens in a new window).
The content of this topic is for informational purposes only. We cannot provide any additional support in using Power BI with the IRIS HR API.
Employee End point example
Launch Power BI
-
Open the Power BI application.
The main interface opens.
Choose Blank report
-
Go to the menu and select Blank report.
-
In the Get Data section, select Blank Query. You can also enter Blank Query into the search bar to quickly find it.
Access Power Query Editor
-
Select Advanced Editor.
-
In the editor, you can create an access token using Power Query to gather data from the IRIS HR API's various endpoints.
-
We have provided pseudo code as an example, based on the employee endpoint.
Copy// First, get the access token
tokenUrl = "https://api.iris.co.uk/oauth2/v1/token",
tokenHeaders = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Authorization" = "Basic {Your credentials in Base64}"
],
tokenBody = "grant_type=client_credentials",
// Function to HTTP POST request for token
GetJsonToken = (url as text, body as text, headers as record) as record =>
let
response = Web.Contents(url, [
Headers = headers,
Content = Text.ToBinary(body)
]),
json = Json.Document(response)
in
json,
// Call the function and retrieve the token data
tokenResult = GetJsonToken(tokenUrl, tokenBody, tokenHeaders),
access = tokenResult[access_token],
// Function to handle the HTTP GET request for employees
GetEmployeeData = (url as text, headers as record) as record =>
let
response = Web.Contents(url, [Headers = headers]),
json = Json.Document(response)
in
json,
// Function to recursively get all pages of employee data and place into a table.
GetAllEmployeeData = (url as text, headers as record, optional accumulatedData as list) as list =>
let
result = GetEmployeeData(url, headers),
nextLink = try result[#"@odata.nextLink"] otherwise null,
data = try result[value] otherwise null,
newAccumulatedData = List.Combine({accumulatedData, data}),
finalData = if nextLink <> null then
@GetAllEmployeeData(nextLink, headers, newAccumulatedData)
else
newAccumulatedData
in
finalData,
// Initial call to get all employee data
employeeUrl = "https://api.iris.co.uk/hr/v2/employees",
employeeHeaders = [
#"Accept" = "application/json;odata.metadata=minimal;odata=true;version1",
#"Authorization" = "Bearer " & access
],
allEmployeeData = GetAllEmployeeData(employeeUrl, employeeHeaders, {}),
#"Converted to Table" = Table.FromList(allEmployeeData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "SourceSystemId", "CreatedOn", "SourceSystemCreatedOn", "CreatedBy", "LastModifiedOn", "SourceSystemLastModifiedOn", "LastModifiedBy", "GenderIdentity", "WindowsUsername", "DisplayId", "TitleHonorific", "FirstName", "KnownAs", "OtherName", "LastName", "CostCentre", "WorkingStatus", "IsManager", "NationalInsuranceNumber", "PayrollId", "TaxCode", "IncludeInPayroll", "EmploymentStartDate", "EmploymentLeftDate", "ContinuousServiceDate", "DateOfBirth", "LastWorkingDate", "Gender", "Ethnicity", "Nationality", "Religion", "LeaverReason", "MaritalStatus", "Phones", "Emails", "Addresses"}, {"Id", "SourceSystemId", "CreatedOn", "SourceSystemCreatedOn", "CreatedBy", "LastModifiedOn", "SourceSystemLastModifiedOn", "LastModifiedBy", "GenderIdentity", "WindowsUsername", "DisplayId", "TitleHonorific", "FirstName", "KnownAs", "OtherName", "LastName", "CostCentre", "WorkingStatus", "IsManager", "NationalInsuranceNumber", "PayrollId", "TaxCode", "IncludeInPayroll", "EmploymentStartDate", "EmploymentLeftDate", "ContinuousServiceDate", "DateOfBirth", "LastWorkingDate", "Gender", "Ethnicity", "Nationality", "Religion", "LeaverReason", "MaritalStatus", "Phones", "Emails", "Addresses"})
in
#"Expanded Column1"Run the Query
-
After pasting the code into the editor, select Done.
-
If a data privacy error message appears, select Continue.
Set Security Level
-
Select the appropriate security level.
Learn more about security levels (opens in a new window).
-
This example is based on choosing a Private security level.
-
Your employee data should display in a table, which you can use for dashboard development.
